﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using KPIS.DBM;
using KPIS.GERP.SERVICE.Model;

namespace KPIS.GERP.SERVICE.DAL
{
    public class RuleApprove
    {
        readonly Employee _employee = new Employee();
        Common _common = new Common();
        readonly DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"]);

        public List<rule_approve> GetRuleApprove(string rule_approve_seq, string rule_approve_type_seq,
            bool isGetRuleApproveType, bool isGetRuleApproveGroup, bool isGetUpperApprover, 
            int? step = null, bool isGetCustomButton = false)
        {
            List<rule_approve> list;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@rule_approve_seq", rule_approve_seq);
                dbManager.AddParameters(2, "@rule_approve_type_seq", rule_approve_type_seq);
                dbManager.AddParameters(3, "@step", step);

                string strSQL = @"select *, case approver_type
                                    when 1 then 'ผู้บังคับบัญชา'
                                    when 2 then 'ระบุตำแหน่ง'
                                    when 3 then 'ระบุสังกัด'
                                    when 4 then 'ระบุทีม'
                                    end as approver_type_name
                                from rule_approve 
                                where record_status <> @delete_status"
                + (!string.IsNullOrWhiteSpace(rule_approve_seq) ? " and rule_approve_seq = @rule_approve_seq" : "")
                + (!string.IsNullOrWhiteSpace(rule_approve_type_seq) ? " and rule_approve_type_seq = @rule_approve_type_seq" : "")
                + (step != null ? " and step = @step" : "");

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<rule_approve>)CollectionHelper.ConvertTo<rule_approve>(dtb);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            List<rule_approve_type> subList = GetRuleApproveType(null, null, isGetRuleApproveGroup); 

            foreach (rule_approve info in list)
            {
                if (isGetRuleApproveType && info.rule_approve_type_seq != null)
                {
                    info.rule_approve_type = (from type in subList
                                              where type.rule_approve_type_seq == info.rule_approve_type_seq
                                              select type).FirstOrDefault();
                } 
            }

            return list;
        } 



        //public List<hrm_ps_employees> GetEmployeeOfRuleApproveData()
         
        public List<rule_approve_type> GetRuleApproveType(string rule_approve_type_seq, string rule_approve_group_seq
            , bool isGetRuleApproveGroup)
        {
            DataTable dtb;
            List<rule_approve_type> list = new List<rule_approve_type>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@rule_approve_type_seq", rule_approve_type_seq);
                dbManager.AddParameters(2, "@rule_approve_group_seq", rule_approve_group_seq);

                string strSQL = "select * from rule_approve_type where record_status <> @delete_status"
                    + (!string.IsNullOrWhiteSpace(rule_approve_type_seq) ? " and rule_approve_type_seq = @rule_approve_type_seq" : "")
                    + (!string.IsNullOrWhiteSpace(rule_approve_group_seq) ? " and rule_approve_group_seq = @rule_approve_group_seq" : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<rule_approve_type>)CollectionHelper.ConvertTo<rule_approve_type>(dtb);

                foreach (rule_approve_type info in list)
                {
                    if (isGetRuleApproveGroup && info.rule_approve_group_seq != null)
                    {
                        List<rule_approve_group> sub_list = GetRuleApproveGroup(info.rule_approve_group_seq.ToString());

                        info.rule_approve_group = sub_list.Count > 0 ? sub_list[0] : null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<rule_approve_group> GetRuleApproveGroup(string rule_approve_group_seq)
        {
            DataTable dtb;
            List<rule_approve_group> list = new List<rule_approve_group>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@rule_approve_group_seq", rule_approve_group_seq);

                string strSQL = "select * from rule_approve_group where record_status <> @delete_status"
                    + (!string.IsNullOrWhiteSpace(rule_approve_group_seq) ? " and rule_approve_group_seq = @rule_approve_group_seq" : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<rule_approve_group>)CollectionHelper.ConvertTo<rule_approve_group>(dtb);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
            return list;
        }

        public List<rule_approve_custom_button> GetRuleApproveCustomButton(int? ruleApproveGroupSeq = null)
        {
            List<rule_approve_custom_button> list;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@deleteStatus", Common.DeletedStatus);
                dbManager.AddParameters(1, "@ruleApproveGroupSeq", ruleApproveGroupSeq);

                string strSQL =
                    @" SELECT rule_approve_custom_seq,
                                          rule_approve_group_seq,
                                          rule_approve_custom_name,
                                          navigate_url,
                                          remarks,
                                          record_status,
                                          created_by,
                                          created_when,
                                          updated_by,
                                          updated_when
                                        FROM rule_approve_custom_button
                                        WHERE record_status <> @deleteStatus
                                        "+ (ruleApproveGroupSeq != null 
                                            ? "AND rule_approve_group_seq = @ruleApproveGroupSeq" 
                                            : string.Empty);

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<rule_approve_custom_button>)CollectionHelper.ConvertTo<rule_approve_custom_button>(dtb);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
            return list;
        }

        #region Insert/Update rule_approve
        public string InsertRuleApprove(string rule_approve_type_seq
            , string step, string approver_type, string approver_seq, string action
            , string remarks, string record_status, string created_by)
        {
            string strSeq = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@rule_approve_seq", null);
                dbManager.AddParameters(1, "@rule_approve_type_seq", rule_approve_type_seq);
                dbManager.AddParameters(2, "@step", step);
                dbManager.AddParameters(3, "@approver_type", approver_type);
                dbManager.AddParameters(4, "@approver_seq", approver_seq);
                dbManager.AddParameters(5, "@action", action);
                dbManager.AddParameters(6, "@remarks", remarks);
                dbManager.AddParameters(7, "@record_status", record_status);
                dbManager.AddParameters(8, "@created_by", created_by);
                dbManager.AddParameters(9, "@created_when", DateTime.Now);

                string strSQL =
@"insert into rule_approve
(rule_approve_type_seq, step, approver_type, approver_seq, action
, remarks, record_status, created_by, created_when)
values(@rule_approve_type_seq, @step, @approver_type, @approver_seq, @action
, @remarks, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = @"select last_insert_id() from rule_approve";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    strSeq = dtb.Rows[0][0].ToString();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
            return strSeq;
        }

        public void UpdateRuleApprove(string rule_approve_seq, string rule_approve_type_seq
            , string step, string approver_type, string approver_seq, string action
            , string remarks, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@rule_approve_seq", rule_approve_seq);
                dbManager.AddParameters(1, "@rule_approve_type_seq", rule_approve_type_seq);
                dbManager.AddParameters(2, "@step", step);
                dbManager.AddParameters(3, "@approver_type", approver_type);
                dbManager.AddParameters(4, "@approver_seq", approver_seq);
                dbManager.AddParameters(5, "@action", action);
                dbManager.AddParameters(6, "@remarks", remarks);
                dbManager.AddParameters(7, "@record_status", record_status);
                dbManager.AddParameters(8, "@updated_by", updated_by);
                dbManager.AddParameters(9, "@updated_when", DateTime.Now);

                string strSQL =
@"update rule_approve
set rule_approve_type_seq = @rule_approve_type_seq
, step = @step
, approver_type = @approver_type
, approver_seq = @approver_seq
, action = @action
, remarks = @remarks
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where rule_approve_seq = @rule_approve_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }

        public void UpdateStatusRuleApprove(string rule_approve_seq, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@rule_approve_seq", rule_approve_seq);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string strSQL =
@"update rule_approve
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where rule_approve_seq = @rule_approve_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }
        #endregion

        #region Insert/Update rule_approve_type
        public string InsertRuleApproveType(string rule_approve_group_seq, string rule_approve_type_name
            , string remarks, string record_status, string created_by)
        {
            string strSeq = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@rule_approve_type_seq", null);
                dbManager.AddParameters(1, "@rule_approve_group_seq", rule_approve_group_seq);
                dbManager.AddParameters(2, "@rule_approve_type_name", rule_approve_type_name);
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@record_status", record_status);
                dbManager.AddParameters(5, "@created_by", created_by);
                dbManager.AddParameters(6, "@created_when", DateTime.Now);

                string strSQL =
@"insert into rule_approve_type
(rule_approve_group_seq, rule_approve_type_name
, remarks, record_status, created_by, created_when)
values(@rule_approve_group_seq, @rule_approve_type_name
, @remarks, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = @"select last_insert_id() from rule_approve_type";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    strSeq = dtb.Rows[0][0].ToString();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
            return strSeq;
        }

        public void UpdateRuleApproveType(string rule_approve_type_seq, string rule_approve_group_seq, string rule_approve_type_name
            , string remarks, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@rule_approve_type_seq", rule_approve_type_seq);
                dbManager.AddParameters(1, "@rule_approve_group_seq", rule_approve_group_seq);
                dbManager.AddParameters(2, "@rule_approve_type_name", rule_approve_type_name);
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@record_status", record_status);
                dbManager.AddParameters(5, "@updated_by", updated_by);
                dbManager.AddParameters(6, "@updated_when", DateTime.Now);

                string strSQL =
@"update rule_approve_type
set rule_approve_group_seq = @rule_approve_group_seq
, rule_approve_type_name = @rule_approve_type_name
, remarks = @remarks
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where rule_approve_type_seq = @rule_approve_type_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }

        public void UpdateStatusRuleApproveType(string rule_approve_type_seq, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@rule_approve_type_seq", rule_approve_type_seq);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string strSQL =
@"update rule_approve_type
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where rule_approve_type_seq = @rule_approve_type_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }
        #endregion

        #region Insert/Update rule_approve_group
        public string InsertRuleApproveGroup(string rule_approve_group_name
            , string remarks, string record_status, string created_by, 
            string rule_approve_group_seq = null,
            
            string ref_to_database = null,
            string ref_to_table_name = null,
            string ref_column_seq_name = null,
            string ref_action_name = null,
            string ref_controller_name = null

            )
        {
            string strSeq = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);
                dbManager.AddParameters(0, "@rule_approve_group_seq", rule_approve_group_seq);
                dbManager.AddParameters(1, "@rule_approve_group_name", rule_approve_group_name);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@record_status", record_status);
                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);
                dbManager.AddParameters(6, "@ref_to_database", ref_to_database);
                dbManager.AddParameters(7, "@ref_to_table_name", ref_to_table_name);
                dbManager.AddParameters(8, "@ref_column_seq_name", ref_column_seq_name);
                dbManager.AddParameters(9, "@ref_action_name", ref_action_name);
                dbManager.AddParameters(10, "@ref_controller_name", ref_controller_name);

                string strSQL =
                    // Alter column
                   /* @"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'rule_approve_group' AND COLUMN_NAME = 'ref_to_database')
  THEN 
  ALTER TABLE rule_approve_group 
  ADD COLUMN ref_to_database varchar(512) DEFAULT NULL;" +

                    @"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'rule_approve_group' AND COLUMN_NAME = 'ref_column_seq_name')
  THEN 
  ALTER TABLE rule_approve_group 
  ADD COLUMN ref_column_seq_name varchar(512) DEFAULT NULL;" + */

                  // add database
@"insert into rule_approve_group
(" + (string.IsNullOrWhiteSpace(rule_approve_group_seq) ? string.Empty:"rule_approve_group_seq, ") + @"rule_approve_group_name
, remarks, record_status, created_by, created_when)
values(" + (string.IsNullOrWhiteSpace(rule_approve_group_seq) ? string.Empty : rule_approve_group_seq + ", ") + @"@rule_approve_group_name
, @remarks, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = @"select last_insert_id() from rule_approve_group";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (string.IsNullOrWhiteSpace(rule_approve_group_seq) && dtb.Rows.Count > 0)
                {
                    strSeq = dtb.Rows[0][0].ToString();
                } else
                {
                    strSeq = rule_approve_group_seq;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return strSeq;
        }

        public void UpdateRuleApproveGroup(string rule_approve_group_seq, string rule_approve_group_name
            , string remarks, string record_status, string updated_by,

            string ref_to_database = null,
            string ref_to_table_name = null,
            string ref_column_seq_name = null,
            string ref_action_name = null,
            string ref_controller_name = null)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);
                dbManager.AddParameters(0, "@rule_approve_group_seq", rule_approve_group_seq);
                dbManager.AddParameters(1, "@rule_approve_group_name", rule_approve_group_name);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@record_status", record_status);
                dbManager.AddParameters(4, "@updated_by", updated_by);
                dbManager.AddParameters(5, "@updated_when", DateTime.Now);
                dbManager.AddParameters(6, "@ref_to_database", ref_to_database);
                dbManager.AddParameters(7, "@ref_to_table_name", ref_to_table_name);
                dbManager.AddParameters(8, "@ref_column_seq_name", ref_column_seq_name);
                dbManager.AddParameters(9, "@ref_action_name", ref_action_name);
                dbManager.AddParameters(10, "@ref_controller_name", ref_controller_name);

                string strSQL =
@"update rule_approve_group
set rule_approve_group_name = @rule_approve_group_name
, remarks = @remarks
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where rule_approve_group_seq = @rule_approve_group_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }

//        -RefSeq
//-ColumnTypeSeq
//-RefToTableName
        public void GetTypeOfRefSeq(int refSeq, string columnTypeSeq, string refToTableName)
        {
            try
            {
//                dbManager.Open(); dbManager.CreateParameters(2); int i = 0;
//                dbManager.AddParameters(i++, "@refSeq", refSeq);
//                dbManager.AddParameters(i++, "@tableName", tableName);

//                string strSQL =
//@"update rule_approve_group
//, record_status = @record_status
//, updated_by = @updated_by
//, updated_when = @updated_when
//where rule_approve_group_seq = @rule_approve_group_seq";

              //  dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }

        public void UpdateStatusRuleApproveGroup(string rule_approve_group_seq, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@rule_approve_group_seq", rule_approve_group_seq);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string strSQL =
@"update rule_approve_group
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where rule_approve_group_seq = @rule_approve_group_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }
        #endregion
         
    }
}
